/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cc.uah.es.snomedctsubsetextractor;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import edu.uci.ics.jung.graph.Graph;
import edu.uci.ics.jung.graph.SparseMultigraph;
import edu.uci.ics.jung.graph.util.EdgeType;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Luis
 */
public class SNOMEDCTDatabase implements SNOMEDCTRepository {

    private SNOMEDCTConfigurator conf;
    private Connection conn;
    private int contConcepts = 0;

    public SNOMEDCTDatabase(SNOMEDCTConfigurator conf) {
        this.conf = conf;
    }

    public SNOMEDCTConfigurator getConf() {
        return conf;
    }

    /**
     * 
     * @param concepts
     * @param relations
     * @param type; type = 0 subsetParent, subsetSons
     */
    public void extractSubset(Collection<SNOMEDConcept> concepts, Collection<SNOMEDRelationshipType> relations, int type) {

        contConcepts = 0;

        openConnection();

        //rellenar el array
        Iterator it = concepts.iterator();
        String strRelationshipeType = "";

        SNOMEDRelationshipType tempRela;

        Object[] array = relations.toArray();

        for (int i = 0; i < array.length - 1; i++) {
            tempRela = (SNOMEDRelationshipType) array[i];
            strRelationshipeType += String.valueOf(tempRela.getRelationshipeType()) + ",";
        }

        tempRela = (SNOMEDRelationshipType) array[array.length - 1];
        strRelationshipeType += String.valueOf(tempRela.getRelationshipeType());

        while (it.hasNext()) {

            SNOMEDConcept concept = (SNOMEDConcept) it.next();

            if (type == 0) {
                subsetParent(concept, strRelationshipeType, concept.getId(), concept.getName());
            }
            if (type == 1) {
                subsetSon(concept, strRelationshipeType, concept.getId(), concept.getName());
            }

        }

        //stmt.close();
        closeConnection();

    }

    private void subsetParent(SNOMEDConcept concept, String relationshipe, int rootConceptId, String rootConcept) {
        try {
            String sql;
            String sql2;
            String sql3;
            PreparedStatement stmt = null;
            PreparedStatement stmt2 = null;
            PreparedStatement stmt3 = null;
            ResultSet rs = null;
            ResultSet rs2 = null;

            sql = "SELECT conceptid1, fullyspecifiedname, relationshiptype, conceptid2 FROM concepts c join relationships r on (c.conceptid = r.conceptid2) where r.conceptid1 = " + concept.getId() + " and CONCEPTSTATUS = 0 and relationshiptype in ( " + relationshipe + ")";

            //System.out.println("Consulta ejecutada: " + sql);
            stmt = this.conn.prepareStatement(sql);

            sql2 = "INSERT INTO subsets VALUES (?,?,?,?,?,?,?);";
            stmt2 = conn.prepareStatement(sql2);

            rs = stmt.executeQuery();

            while (rs.next()) {
                sql3 = "SELECT count(*) as num FROM subsets s where conceptidRoot = " + concept.getId() + " and conceptidSon = " + rs.getString("conceptid2") + " and RelationshipType = " + rs.getString("relationshiptype");
                //System.out.println(sql3);
                stmt3 = conn.prepareStatement(sql3);

                rs2 = stmt3.executeQuery();
                rs2.last();

                if (rs2.getInt("num") == 0) {

                    //System.out.println("Añadiendo a la lista el id: " + rs.getString("conceptid2") + " cuyo nombre es: " + rs.getString("fullyspecifiedname"));
                    stmt2.setInt(1, rootConceptId);
                    stmt2.setString(2, rootConcept);
                    stmt2.setInt(3, rs.getInt("conceptid2"));
                    stmt2.setString(4, rs.getString("fullyspecifiedname"));
                    stmt2.setInt(5, concept.getId());
                    stmt2.setString(6, concept.getName());
                    stmt2.setInt(7, rs.getInt("relationshiptype"));
                    stmt2.executeUpdate();

                    contConcepts++;

                }

                SNOMEDConcept conceptSon = new SNOMEDConcept(rs.getInt("conceptid2"), rs.getString("fullyspecifiedname"), concept.getId(), concept.getName());

                subsetParent(conceptSon, relationshipe, rootConceptId, rootConcept);

            }
        } catch (SQLException ex) {
            Logger.getLogger(SNOMEDCTDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }


    }

    private void subsetSon(SNOMEDConcept concept, String relationshipe, int rootConceptId, String rootConcept) {
        try {
            String sql;
            String sql2;
            String sql3;
            PreparedStatement stmt = null;
            PreparedStatement stmt2 = null;
            PreparedStatement stmt3 = null;
            ResultSet rs = null;
            ResultSet rs2 = null;

            sql = "SELECT conceptid1, fullyspecifiedname, relationshiptype, conceptid2 FROM concepts c join relationships r on (c.conceptid = r.conceptid1) where r.conceptid2 = " + concept.getId() + " and CONCEPTSTATUS = 0 and relationshiptype in ( " + relationshipe + ")";

            //System.out.println("Consulta ejecutada: " + sql);
            stmt = this.conn.prepareStatement(sql);

            sql2 = "INSERT INTO subsets VALUES (?,?,?,?,?,?,?);";
            stmt2 = conn.prepareStatement(sql2);

            rs = stmt.executeQuery();

            while (rs.next()) {
                sql3 = "SELECT count(*) as num FROM subsets s where conceptidRoot = " + concept.getId() + " and conceptidSon = " + rs.getString("conceptid1") + " and RelationshipType = " + rs.getString("relationshiptype");
                //System.out.println(sql3);
                stmt3 = conn.prepareStatement(sql3);

                rs2 = stmt3.executeQuery();
                rs2.last();

                if (rs2.getInt("num") == 0) {

                    //System.out.println("Añadiendo a la lista el id: " + rs.getString("conceptid1") + " cuyo nombre es: " + rs.getString("fullyspecifiedname"));
                    stmt2.setInt(1, rootConceptId);
                    stmt2.setString(2, rootConcept);
                    stmt2.setInt(3, rs.getInt("conceptid1"));
                    stmt2.setString(4, rs.getString("fullyspecifiedname"));
                    stmt2.setInt(5, concept.getId());
                    stmt2.setString(6, concept.getName());
                    stmt2.setInt(7, rs.getInt("relationshiptype"));
                    stmt2.executeUpdate();

                    contConcepts++;

                }

                SNOMEDConcept conceptSon = new SNOMEDConcept(rs.getInt("conceptid1"), rs.getString("fullyspecifiedname"), concept.getId(), concept.getName());

                subsetSon(conceptSon, relationshipe, rootConceptId, rootConcept);

            }
        } catch (SQLException ex) {
            Logger.getLogger(SNOMEDCTDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public void extractSubset(Collection<SNOMEDConcept> concepts, Collection<SNOMEDRelationshipType> relations, int type, int prof) {

        contConcepts = 0;

        openConnection();

        //rellenar el array
        Iterator it = concepts.iterator();
        String strRelationshipeType = "";

        SNOMEDRelationshipType tempRela;

        Object[] array = relations.toArray();

        for (int i = 0; i < array.length - 1; i++) {
            tempRela = (SNOMEDRelationshipType) array[i];
            strRelationshipeType += String.valueOf(tempRela.getRelationshipeType()) + ",";
        }

        tempRela = (SNOMEDRelationshipType) array[array.length - 1];
        strRelationshipeType += String.valueOf(tempRela.getRelationshipeType());

        while (it.hasNext()) {

            SNOMEDConcept concept = (SNOMEDConcept) it.next();

            if (type == 0) {
                subsetParentProf(concept, strRelationshipeType, concept.getId(), concept.getName(), prof);
            }
            if (type == 1) {
                subsetSonProf(concept, strRelationshipeType, concept.getId(), concept.getName(), prof);
            }

        }

        closeConnection();

    }

    private void subsetParentProf(SNOMEDConcept concept, String relationshipe, int rootConceptId, String rootConcept, int profundidad) {
        try {
            String sql;
            String sql2;
            String sql3;
            PreparedStatement stmt = null;
            PreparedStatement stmt2 = null;
            PreparedStatement stmt3 = null;
            ResultSet rs = null;
            ResultSet rs2 = null;

            sql = "SELECT conceptid1, fullyspecifiedname, relationshiptype, conceptid2 FROM concepts c join relationships r on (c.conceptid = r.conceptid2) where r.conceptid1 = " + concept.getId() + " and CONCEPTSTATUS = 0 and relationshiptype in ( " + relationshipe + ")";

            //System.out.println("Consulta ejecutada: " + sql);
            stmt = this.conn.prepareStatement(sql);

            sql2 = "INSERT INTO subsets VALUES (?,?,?,?,?,?,?);";
            stmt2 = conn.prepareStatement(sql2);

            rs = stmt.executeQuery();

            while (rs.next()) {
                sql3 = "SELECT count(*) as num FROM subsets s where conceptidRoot = " + concept.getId() + " and conceptidSon = " + rs.getString("conceptid2") + " and RelationshipType = " + rs.getString("relationshiptype");
                //System.out.println(sql3);
                stmt3 = conn.prepareStatement(sql3);

                rs2 = stmt3.executeQuery();
                rs2.last();

                if (rs2.getInt("num") == 0) {

                    //System.out.println("Añadiendo a la lista el id: " + rs.getString("conceptid2") + " cuyo nombre es: " + rs.getString("fullyspecifiedname"));
                    stmt2.setInt(1, rootConceptId);
                    stmt2.setString(2, rootConcept);
                    stmt2.setInt(3, rs.getInt("conceptid2"));
                    stmt2.setString(4, rs.getString("fullyspecifiedname"));
                    stmt2.setInt(5, concept.getId());
                    stmt2.setString(6, concept.getName());
                    stmt2.setInt(7, rs.getInt("relationshiptype"));
                    stmt2.executeUpdate();

                    contConcepts++;

                }

                if (profundidad > 1) {

                    SNOMEDConcept conceptSon = new SNOMEDConcept(rs.getInt("conceptid2"), rs.getString("fullyspecifiedname"), concept.getId(), concept.getName());

                    subsetParentProf(conceptSon, relationshipe, rootConceptId, rootConcept, profundidad - 1);

                }

            }
        } catch (SQLException ex) {
            Logger.getLogger(SNOMEDCTDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void subsetSonProf(SNOMEDConcept concept, String relationshipe, int rootConceptId, String rootConcept, int profundidad) {
        try {
            String sql;
            String sql2;
            String sql3;
            PreparedStatement stmt = null;
            PreparedStatement stmt2 = null;
            PreparedStatement stmt3 = null;
            ResultSet rs = null;
            ResultSet rs2 = null;

            sql = "SELECT conceptid1, fullyspecifiedname, relationshiptype, conceptid2 FROM concepts c join relationships r on (c.conceptid = r.conceptid1) where r.conceptid2 = " + concept.getId() + " and CONCEPTSTATUS = 0 and relationshiptype in ( " + relationshipe + ")";

            //System.out.println("Consulta ejecutada: " + sql);
            stmt = this.conn.prepareStatement(sql);

            sql2 = "INSERT INTO subsets VALUES (?,?,?,?,?,?,?);";
            stmt2 = conn.prepareStatement(sql2);

            rs = stmt.executeQuery();

            while (rs.next()) {
                sql3 = "SELECT count(*) as num FROM subsets s where conceptidRoot = " + concept.getId() + " and conceptidSon = " + rs.getString("conceptid1") + " and RelationshipType = " + rs.getString("relationshiptype");
                //System.out.println(sql3);
                stmt3 = conn.prepareStatement(sql3);

                rs2 = stmt3.executeQuery();
                rs2.last();

                if (rs2.getInt("num") == 0) {

                    //System.out.println("Añadiendo a la lista el id: " + rs.getString("conceptid1") + " cuyo nombre es: " + rs.getString("fullyspecifiedname"));
                    stmt2.setInt(1, rootConceptId);
                    stmt2.setString(2, rootConcept);
                    stmt2.setInt(3, rs.getInt("conceptid1"));
                    stmt2.setString(4, rs.getString("fullyspecifiedname"));
                    stmt2.setInt(5, concept.getId());
                    stmt2.setString(6, concept.getName());
                    stmt2.setInt(7, rs.getInt("relationshiptype"));
                    stmt2.executeUpdate();

                    contConcepts++;

                }


                if (profundidad > 1) {

                    SNOMEDConcept conceptSon = new SNOMEDConcept(rs.getInt("conceptid1"), rs.getString("fullyspecifiedname"), concept.getId(), concept.getName());

                    subsetSonProf(conceptSon, relationshipe, rootConceptId, rootConcept, profundidad - 1);

                }

            }
        } catch (SQLException ex) {
            Logger.getLogger(SNOMEDCTDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public Graph<String, String> getGraph(Collection<SNOMEDConcept> concepts) {

        Graph<String, String> g = new SparseMultigraph<String, String>();
        SNOMEDConcept tempConcept;

        HashMap<Integer, String> hmConcepts = new HashMap<Integer, String>();
        String concept1;
        String concept2;

        try {
            String sql;
            //PreparedStatement stmt = null;
            ResultSet rs = null;

            openConnection();

            Statement stmt = (Statement) this.conn.createStatement(
                    java.sql.ResultSet.TYPE_FORWARD_ONLY,
                    java.sql.ResultSet.CONCUR_READ_ONLY);

            stmt.setFetchSize(Integer.MIN_VALUE);


            String strConcepts = "";

            Object[] array = concepts.toArray();

            for (int i = 0; i < array.length - 1; i++) {
                tempConcept = (SNOMEDConcept) array[i];
                strConcepts += String.valueOf(tempConcept.getId()) + ",";
                hmConcepts.put(tempConcept.getId(), tempConcept.getName());
                if (g.containsVertex(tempConcept.getName()) == false) {
                    g.addVertex(tempConcept.getName());
                }

            }


            tempConcept = (SNOMEDConcept) array[array.length - 1];
            strConcepts += String.valueOf(tempConcept.getId());
            hmConcepts.put(tempConcept.getId(), tempConcept.getName());
            if (g.containsVertex(tempConcept.getName()) == false) {
                g.addVertex(tempConcept.getName());
            }

            //rellenar el array
            Iterator it = concepts.iterator();
            while (it.hasNext()) {

                SNOMEDConcept concept = (SNOMEDConcept) it.next();

                sql = "SELECT conceptid1, conceptid2, RELATIONSHIPTYPE FROM relationships r where r.conceptid1 = " + concept.getId() + " and conceptid2 in (" + strConcepts + ")";

                rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    concept1 = hmConcepts.get(rs.getInt("conceptid1"));
                    concept2 = hmConcepts.get(rs.getInt("conceptid2"));

                    if (g.containsEdge(concept1 + "-" + concept2) == false) {
                        g.addEdge(concept1 + "-" + concept2 + "[" + rs.getString("RELATIONSHIPTYPE") + "]", concept1, concept2, EdgeType.DIRECTED);
                    }
                }

                sql = "SELECT conceptid1, conceptid2, RELATIONSHIPTYPE FROM relationships r where r.conceptid1 in (" + strConcepts + ") and conceptid2 = " + concept.getId();

                rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    concept1 = hmConcepts.get(rs.getInt("conceptid1"));
                    concept2 = hmConcepts.get(rs.getInt("conceptid2"));

                    if (g.containsEdge(concept1 + "-" + concept2) == false) {
                        g.addEdge(concept1 + "-" + concept2 + "[" + rs.getString("RELATIONSHIPTYPE") + "]", concept1, concept2, EdgeType.DIRECTED);
                    }
                }

            }

            rs.close();
            stmt.close();
            closeConnection();


            //System.out.println("The graph g = " + g.toString());

        } catch (SQLException ex) {
            Logger.getLogger(SNOMEDCTDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return g;



    }

    private void openConnection() {

        if (this.conf.getValue("sgbd").compareToIgnoreCase("mysql") == 0) {
            try {

                Class.forName("com.mysql.jdbc.Driver").newInstance();
                this.conn = (Connection) DriverManager.getConnection("jdbc:mysql:" + this.conf.getValue("url") + this.conf.getValue("schema"), this.conf.getValue("user"), this.conf.getValue("password"));

            } catch (SQLException ex) {
                Logger.getLogger(SNOMEDCTDatabase.class.getName()).log(Level.SEVERE, null, ex);
            } catch (InstantiationException ex) {
                Logger.getLogger(SNOMEDCTDatabase.class.getName()).log(Level.SEVERE, null, ex);
            } catch (IllegalAccessException ex) {
                Logger.getLogger(SNOMEDCTDatabase.class.getName()).log(Level.SEVERE, null, ex);
            } catch (ClassNotFoundException ex) {
                Logger.getLogger(SNOMEDCTDatabase.class.getName()).log(Level.SEVERE, null, ex);
            }

        } else {
            throw new UnsupportedOperationException("Not supported yet.");
        }

    }

    private void closeConnection() {

        try {
            this.conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(SNOMEDCTDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public int getNumConcepts() {
        return this.contConcepts;
    }
}

class MyLink {

    double weight; // should be private for good practice
    String edgeName;

    public MyLink(double weight, String edgeName) {
        this.edgeName = edgeName; // This is defined in the outer class.
        this.weight = weight;

    }

    public String toString() { // Always good for debugging
        return "E" + this.edgeName;
    }
}